Re: [SQL] Cursors and waiting users

At 16:03 +0300 on 09/06/1999, Szcząchor Tomasz wrote:


> I can't finish this command until I close transaction on first session.
> Why?
> Does the declaring of cursor lock table for insert and I can't insert into
> table? But I use cursor only for viewing of table.
>
>
> Than,  if one user use cursor on the table, second one can't  insert data to
> that table?
>
> I am beginner in postgress. Help me find my mistakes.
> (Sorry for my English).

Yes. In all releases up to and including 6.4.2, the entire table is locked
for operations that require sharing of data.

You ask why it locks if it only reads the table. Well, the reason is that
the other person, doing the insert, is asking for an exclusive lock. And
you are holding a shared lock. You can have several people reading the
table at the same time, but if there is one that wants to write, he can
only get his exclusive lock when they all finish to read.

Why? Because if he gets a permission to write before you finish the read,
and updates some of the rows, you may get different data in the middle of
your cursor transaction. You start reading, then you go back to the same
row - but it's different, in the same transaction. The way PostgreSQL works
now prevents such differences in data in the middle of a transaction.

In the coming version, there is a different locking logic.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma